use chrono::{DateTime, Local, Utc};
use sqlx::{sqlite::SqlitePool, Error};
use std::time::Duration;
use tokio::time::interval;
use serde::{Deserialize, Serialize};
use tokio::runtime::Runtime;

// 日志结构体
#[derive(Serialize, Deserialize, Clone, Debug, sqlx::FromRow)] 
pub struct LogEntry {
    pub id: i64,
    pub username: String,
    pub level: String,
    pub ip: String,
    pub message: String,
    pub detail: String,
    pub timestamp: String,
}

// 初始化数据库连接池并创建日志表
pub async fn log_init_db() -> Result<SqlitePool, Error> {
    // 构建数据库路径（使用当前工作目录）
    let db_path = std::env::current_dir()?
        .join("log")
        .join("logs.db");
    
    // 确保目录存在
    if let Some(parent) = db_path.parent() {
        std::fs::create_dir_all(parent)?;
    }
   // 检查文件是否存在（可选，SQLite 会自动创建）
    if !db_path.exists() {
        // 手动创建空文件（SQLite 会自动处理，但这步可提供更明确的错误）
        std::fs::File::create(&db_path)?;
        
        println!("Created new database file: {}", db_path.display());
    } else {
        //println!("Using existing database file: {}", db_path.display());
    }
    // 创建数据库连接池
    let db_url = db_path.to_str();

    let pool = SqlitePool::connect(db_url.expect("not-found")).await?;

    sqlx::query(
        "CREATE TABLE IF NOT EXISTS logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username text not null,
            level text not null,
            ip text not null,
            message TEXT NOT NULL,
            detail TEXT NOT NULL,
            timestamp TEXT NOT NULL
        )",
    )
   .execute(&pool)
   .await?;
    Ok(pool)
}
pub fn write_log(
    username: String,
    level: String,
    ip: String,
    message: String,
    detail: String,
) -> Result<(), Error> {
    // 创建一个独立的运行时来执行异步操作
    let rt = Runtime::new().map_err(|e| e)?;
    
    rt.block_on(async {
        let pool = log_init_db().await?;
        
        let timestamp = Utc::now().format("%Y-%m-%dT%H:%M:%S").to_string();
        
        sqlx::query(
            "INSERT INTO logs (username, level, ip, message, detail, timestamp) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
        )
        .bind(username)
        .bind(level)
        .bind(ip)
        .bind(message)
        .bind(detail)
        .bind(timestamp)
        .execute(&pool)
        .await?;
        Ok(())
    })
}
// 将函数标记为 async
pub async fn write_log_async(
    username: String,
    level: String,
    ip: String,
    message: String,
    detail: String,
) -> Result<(), sqlx::Error> {
    // 直接调用异步初始化函数获取数据库连接池
    let pool = log_init_db().await?;
    
    // 保持时间戳生成方式不变
    // 获取当前 UTC 时间（PrimitiveDateTime + UTC 偏移）
    let now: DateTime<Local> = Local::now();
    // 转换为本地时间（自动获取系统时区）
   
    // 格式化为字符串
    let timestamp = now.format("%Y-%m-%d %H:%M:%S").to_string();

    
    // 执行异步 SQL 查询
    sqlx::query(
        "INSERT INTO logs (username, level, ip, message, detail, timestamp) VALUES ($1, $2, $3, $4, $5, $6)",
    )
    .bind(username)
    .bind(level)
    .bind(ip)
    .bind(message)
    .bind(detail)
    .bind(timestamp)
    .execute(&pool)
    .await?;
    
    Ok(())
}

// 读取所有日志
pub async fn read_logs(pool: &SqlitePool) -> Result<Vec<LogEntry>, Error> {
    let logs = sqlx::query_as::<_, LogEntry>(
        "SELECT id, username, level, ip, message, detail, timestamp FROM logs",
    )
   .fetch_all(pool)
   .await?;
    Ok(logs)
}

// 定期删除旧日志（例如保留一周内的日志）
pub async fn delete_old_logs() {
    let pool = log_init_db().await.expect("Failed to initialize database");
    let mut interval = interval(Duration::from_secs(24 * 60 * 60)); // 每天执行一次

    loop {
        interval.tick().await;
        let half_year_ago = Utc::now() - chrono::Duration::days(185);
        let timestamp = half_year_ago.to_rfc3339();
        match sqlx::query("DELETE FROM logs WHERE timestamp < ?1")
           .bind(timestamp)
           .execute(&pool)
           .await
        {
            Ok(_) => println!("Old logs cleared successfully."),
            Err(e) => eprintln!("Error clearing old logs: {}", e),
        }
    }
}
